﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class HoSoBenhNhanDAO
    {
        public static DataTable LayDanhSachHoSoBenhNhan()
        {
            string sql;
            sql = "SELECT * FROM HoSoBenhNhan";
            return DataAccess.ExecQuery(sql);
        }

        public static void ThemHoSoBenhNhan(HoSoBenhNhanDTO obj)
        {
            string sql;
            sql = string.Format(@"INSERT INTO HoSoBenhNhan(HoTen, NgaySinh, GioiTinh, DiaChi, DienThoai, CMND, NhanVienPhuTrach, SoTheBaoHiem, NgayHetHan) VALUES(N'{0}', N'{1}', N'{2}', N'{3}', N'{4}', N'{5}', N'{6}', N'{7}', N'{8}')",
                                obj.HoTen, obj.NgaySinh, obj.GioiTinh, obj.DiaChi, obj.DienThoai, obj.CMND, obj.NhanVienPhuTrach, obj.SoTheBaoHiem, obj.NgayHetHan);
            DataAccess.ExecNonQuery(sql);

            string sql1 = "update HoSoBenhNhan set MaHSBN = 'HS' + CAST(MaHoSoBenhNhan as nvarchar(10))";
            DataAccess.ExecNonQuery(sql1);
        }

        public static void SuaHoSoBenhNhan(HoSoBenhNhanDTO obj)
        {
            string sql;
            sql = string.Format(@"UPDATE HoSoBenhNhan SET HoTen = N'{0}', NgaySinh = N'{1}', GioiTinh = N'{2}', DiaChi = N'{3}', DienThoai = N'{4}', CMND = N'{5}', NhanVienPhuTrach = N'{6}',  SoTheBaoHiem = N'{7}',  NgayHetHan = N'{8}' 
                WHERE MaHoSoBenhNhan = '{9}'", obj.HoTen, obj.NgaySinh, obj.GioiTinh, obj.DiaChi, obj.DienThoai, obj.CMND, obj.NhanVienPhuTrach, obj.SoTheBaoHiem, obj.NgayHetHan, obj.MaHoSoBenhNhan);
            DataAccess.ExecNonQuery(sql);
        }

        public static void XoaHoSoBenhNhan(int maHoSoBenhNhan)
        {
            string sql;
            sql = string.Format("DELETE HoSoBenhNhan WHERE MaHoSoBenhNhan = {0}", maHoSoBenhNhan);
            DataAccess.ExecNonQuery(sql);
        }

        public static HoSoBenhNhanDTO TraCuuBenhNhan(HoSoBenhNhanDTO obj)
        {
            HoSoBenhNhanDTO kq = new HoSoBenhNhanDTO();
            string sql;
            sql = string.Format(@"select * from HoSoBenhNhan where MaHoSoBenhNhan = {0} or HoTen= '{1}'", obj.MaHoSoBenhNhan, obj.HoTen);
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaHoSoBenhNhan = Convert.ToInt32(dt.Rows[0]["MaHoSoBenhNhan"]);
                kq.MaHSBN = Convert.ToString(dt.Rows[0]["MaHSBN"]);
                kq.HoTen = Convert.ToString(dt.Rows[0]["HoTen"]);
                kq.NgaySinh = Convert.ToDateTime(dt.Rows[0]["NgaySinh"]);
                kq.GioiTinh = Convert.ToString(dt.Rows[0]["GioiTinh"]);
                kq.DiaChi = Convert.ToString(dt.Rows[0]["DiaChi"]);
                kq.DienThoai = Convert.ToString(dt.Rows[0]["DienThoai"]);
                kq.CMND = Convert.ToString(dt.Rows[0]["CMND"]);
                kq.NhanVienPhuTrach = Convert.ToString(dt.Rows[0]["NhanVienPhuTrach"]);
                kq.SoTheBaoHiem = Convert.ToString(dt.Rows[0]["SoTheBaoHiem"]);
                kq.NgayHetHan = Convert.ToDateTime(dt.Rows[0]["NgayHetHan"]);
            }
            return kq;
        }


        public static HoSoBenhNhanDTO LayThongTinHoSoBenhNhanTheoMaHoSoBenhNhan(int maHoSoBenhNhan)
        {
            HoSoBenhNhanDTO kq = new HoSoBenhNhanDTO();
            string sql;
            sql = @"select * from HoSoBenhNhan where MaHoSoBenhNhan = " + maHoSoBenhNhan;
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaHoSoBenhNhan = Convert.ToInt32(dt.Rows[0]["MaHoSoBenhNhan"]);
                kq.MaHSBN = Convert.ToString(dt.Rows[0]["MaHSBN"]);
                kq.HoTen = Convert.ToString(dt.Rows[0]["HoTen"]);
                kq.NgaySinh = Convert.ToDateTime(dt.Rows[0]["NgaySinh"]);
                kq.GioiTinh = Convert.ToString(dt.Rows[0]["GioiTinh"]);
                kq.DiaChi = Convert.ToString(dt.Rows[0]["DiaChi"]);
                kq.DienThoai = Convert.ToString(dt.Rows[0]["DienThoai"]);
                kq.CMND = Convert.ToString(dt.Rows[0]["CMND"]);
                kq.NhanVienPhuTrach = Convert.ToString(dt.Rows[0]["NhanVienPhuTrach"]);
                kq.SoTheBaoHiem = Convert.ToString(dt.Rows[0]["SoTheBaoHiem"]);
                kq.NgayHetHan = Convert.ToDateTime(dt.Rows[0]["NgayHetHan"]);
            }
            return kq;
        }

        public static DataTable TraCuuBenhNhan(string sql)
        {
            DataTable dt = DataAccess.ExecQuery(sql);
            return dt;
        }

    }
}
